package com.appkubes.mall.restful.application.user.account.history.dao;

import com.appkubes.mall.core.dao.BaseMallUserAccountHistoryDao;
import com.appkubes.mall.restful.application.user.account.account.entity.dto.AccountHistoryDTO;
import com.appkubes.mall.restful.application.user.account.account.entity.dto.StatisBasic;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @Author: Rongjie
 * @Date: 2020/1/13 21:56
 * @Description:
 */
@Mapper
@Repository
public interface UserAccountHistoryDao extends BaseMallUserAccountHistoryDao {

    @Select({"SELECT history.* ",
            "        ,user.tel, DATE_FORMAT(history.create_time, '%Y-%m-%d') as create_time_str ",
            " FROM mall_user_account_history as history ",
            "  LEFT JOIN mall_order as order1 ON history.source_key=order1.order_key",
            "  LEFT JOIN mall_user as user ON user.user_key=order1.user_key" ,
            " WHERE history.user_key= #{userKey} ",
            "   AND history.state= #{state} ",
            " ORDER BY history.create_time DESC"})
    List<AccountHistoryDTO> geHistory(@Param("userKey") String userKey, @Param("state") String state);

    @Select("select t1.d as day, sum(t1.bill_num) as order_count, sum(t1.brokerage) as total_brokerage" +
            " from (" +
            " select COALESCE(1) as bill_num, t1.d, t2.brokerage from (" +
            " select DATE_SUB(curdate(), INTERVAL 1 month) as d" +
            " union all" +
            " select DATE_SUB(curdate(), INTERVAL 0 day) as d" +
            " )t1 inner join mall_user_account_history t2 on t2.create_time > t1.d and t2.state='CONFIRM' and t2.user_key=#{userKey}" +
            ")t1" +
            " GROUP BY t1.d")
    List<StatisBasic> accountStatis(@Param("userKey") String userKey);
}
